import re
import sqlite3
from typing import Dict, Optional

def extract_teacher_info(text: str) -> Optional[Dict]:
    """从文本中提取老师信息"""
    try:
        # 提取ID (@后面的部分)
        id_match = re.search(r'@(\w+)', text)
        tele_id = id_match.group(1) if id_match else None
        
        # 提取昵称
        name_match = re.search(r'名字 Name：(.+)', text)
        nickname = name_match.group(1).strip() if name_match else None
        
        # 提取价格
        price_match = re.search(r'费用 Cost：(\d+)P', text)
        price = int(price_match.group(1)) if price_match else None
        
        # 提取城市和区域
        area_match = re.search(r'地区 Area：#(.+?)\s*#(.+)', text)
        city = area_match.group(1).strip() if area_match else None
        district = area_match.group(2).strip() if area_match else None
        
        # 提取出生年月、身高、体重
        data_match = re.search(r'资料 Data：(\d+)年\s*(\d+)cm\s*(\d+)kg', text)
        bornyear = int(f"20{data_match.group(1)}") if data_match else None
        height = int(data_match.group(2)) if data_match else None
        weight = int(data_match.group(3)) if data_match else None
        
        if not all([tele_id, nickname]):
            print('提取失败')
            return None
            
        return {
            'teleID': tele_id,
            'nickname': nickname,
            'P': price,
            'city': city,
            'district': district,
            'bornyear': bornyear,
            'height': height,
            'weight': weight
        }
    except Exception as e:
        print(f"Error extracting info: {e}")
        return None

def save_to_db(teacher_info: Dict) -> bool:
    """将老师信息保存到数据库"""
    try:
        conn = sqlite3.connect('db/teachers.sqlite')
        cursor = conn.cursor()
        
        # 创建或更新表结构
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS teacher (
            teleID TEXT PRIMARY KEY,
            nickname TEXT NOT NULL,
            P INTEGER,
            city TEXT,
            district TEXT,
            bornyear INTEGER,
            height INTEGER,
            weight INTEGER
        )
        ''')
        
        # 插入或更新记录
        cursor.execute('''
        INSERT OR REPLACE INTO teacher 
        (teleID, nickname, P, city, district, bornyear, height, weight)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            teacher_info['teleID'],
            teacher_info['nickname'],
            teacher_info['P'],
            teacher_info['city'],
            teacher_info['district'],
            teacher_info['bornyear'],
            teacher_info['height'],
            teacher_info['weight']
        ))
        
        conn.commit()
        conn.close()
        print(teacher_info)
        return True
    except Exception as e:
        print(f"Error saving to DB: {e}")
        return False

def process_teacher_text(text: str) -> bool:
    """处理老师文本并保存到数据库"""
    teacher_info = extract_teacher_info(text)
    if not teacher_info:
        return False
    return save_to_db(teacher_info)

# 示例用法
if __name__ == "__main__":
    sample_text = """
名字Name：杏月 Luna

地区 Area：#上海 #普陀

资料 Data：04年 172cm 47kg C

特点 Features：新加坡在校留学生 短开兼职 清纯女大

费用 Cost：2500P

联系 Tel： @xingyueii

标签 Label：#网红 #学生 #纯御
    """
    
    if process_teacher_text(sample_text):
        print("信息提取并保存成功")
    else:
        print("信息提取或保存失败")


